﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace CIT.WMS.Product.Bll
{
    class Bll_ProductOut
    {
        /// <summary>
        /// 获取出库单列表
        /// </summary>
        /// <returns></returns>
        public static DataTable GetOutStockList(string billType)
        {
            string sqlcmd = string.Format(@"SELECT * FROM [dbo].[wms_T_OutStock] WHERE Status='0' and billType='{0}'", billType);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }

        public static DataTable GetOutStockList()
        {
            string sqlcmd = string.Format(@"SELECT * FROM [dbo].[wms_T_OutStock] WHERE Status='0' and Updator is not null and (billType='寄售出库' or billType='销售订单' or billType='返工出库单' or billType='手工出库单' or billType='销售计划' or billType='NG返工出库单')");
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }

        public static DataTable GetOutStockListConfirm(string billno)
        {
            string sqlcmd = string.Format(@"SELECT * FROM [dbo].[wms_T_OutStock] WHERE Status='0' and (billType='寄售出库' or billType='销售订单' or billType='返工出库单' or billType='手工出库单' or billType='销售计划' or billType='NG返工出库单') and BillNo = '{0}'", billno);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }

        public static DataTable GetOutStockLists(string billno)
        {
            string sqlcmd = string.Format(@"SELECT * FROM [dbo].[wms_T_OutStock] WHERE Status='0' and (billType='报废出库单' or billType='寄售出库' or billType='销售订单' or billType='返工出库单' or billType='NG返工出库单' or billType='手工出库单' or billType='销售计划') and BillNo='{0}'", billno);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }

        public static bool GetOBA2Flg(string billNo)
        {
            string sqlcmd = @"select isnull(IsOba,'') from wms_T_OutStock a 
inner join wms_T_OBACustomer c on a.CustomerSN=c.CustomerSN 
where a.Status='0' and a.billType='销售订单' and a.BillNo='{0}'";
            sqlcmd = string.Format(sqlcmd, billNo);
            var dt = Utils.NMS.GetDataTable(sqlcmd);
            if (dt.Rows.Count == 0)
            {
                return true;
            }
            else
            {
                if (dt.Rows[0][0].ToString().Equals("1"))
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }

        public static DataTable GetHoldBox(string boxno)
        {
            //string sqlcmd = string.Format(@"SELECT * FROM [dbo].[TABLE_HOLD] WHERE CARTONNO='{0}'", boxno);
            string sqlcmd = "select * from OPENQUERY(MES_125,'select * from VIEW_HOLD where PackageCode=''{0}''')";
            sqlcmd = string.Format(sqlcmd, boxno);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            if (result.Rows.Count == 0)
            {
                sqlcmd = "SELECT * FROM OPENQUERY(HYMES_LINK_SN, 'SELECT * FROM SAA_MES.VIEW_HOLD where cartonno=''{0}''') AS tp";
                sqlcmd = string.Format(sqlcmd, boxno);
                result = Utils.NMS.GetDataTable(sqlcmd);
            }
            return result;
        }

        public static DataTable GetBoxNum(string boxno)
        {
            string sqlcmd = string.Format(@"SELECT PutawayQty FROM [dbo].[wms_T_InStockRowDetails] 
                                            WHERE BoxNo='{0}' and Status<>'H' ORDER BY Status", boxno);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }

        public static DataTable GetEndOutStockList()
        {
            string sqlcmd = string.Format(@"SELECT * FROM [dbo].[wms_T_OutStock] WHERE Status='2' and (billType='寄售出库' or billType='销售订单' or billType='返工出库单' or billType='手工出库单')");
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }

        public static DataTable GetEndOutStockLists(string billno)
        {
            string sqlcmd = string.Format(@"SELECT * FROM [dbo].[wms_T_OutStock] WHERE Status='2' and (billType='寄售出库' or billType='销售订单' or billType='返工出库单' or billType='手工出库单') and BillNo='{0}'", billno);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }

        public static DataTable GetEndStockBoxCodeList(string BillNo)
        {
            string sqlcmd = string.Format(@"SELECT * FROM [dbo].[wms_T_OutStockRowDetails] WHERE BillNo='{0}'", BillNo);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }

        public static DataTable GetInventoryeList(string location_sn)
        {
            string sqlcmd = string.Format(@"SELECT * FROM [dbo].[wms_T_Inventory] WHERE location_sn='{0}'", location_sn);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }

        private static DataTable GetRecommendLocationSN(string regionName, string storageSN, string billNo)
        {
            string sql = string.Format(@"SELECT os.RelatedBill,sr.* FROM dbo.wms_T_outStock os
LEFT JOIN dbo.wms_T_OutStockRows sr ON os.BillNo=sr.BillNo
WHERE os.BillNo='{0}' and  ISNULL( sr.PlanQty,0)> ISNULL(sr.SelectQty,0)", billNo);
            var stockRowsDT = Utils.NMS.GetDataTable(sql);
            DataTable dt = new DataTable();
            foreach (DataRow row in stockRowsDT.Rows)
            {
                var relatedBill = row["RelatedBill"].ToString();
                var productNumber = row["ProductNumber"].ToString();
                var planQty = 0;
                if (!string.IsNullOrEmpty(row["PlanQty"].ToString()))
                {
                    planQty = Convert.ToInt32(row["PlanQty"]);
                }

                var rowNo = row["RowNo"].ToString();

                //                var deleteSql = string.Format(@"DELETE FROM [dbo].[wms_T_OutStockRowDetails] 
                //WHERE Status='0' AND BillNo='{0}' AND RowNo='{1}'", billNo, rowNo);
                //                Utils.NMS.ExcuteSQL(deleteSql);

                var lotSql = string.Format(@"SELECT * FROM wms_R_LotSelect WHERE RelatedBill='{0}' AND PartNumber='{1}'", relatedBill, productNumber);

                var lotSelect = Utils.NMS.GetDataTable(lotSql);

                var rowSql = string.Empty;
                if (lotSelect.Rows.Count == 0)
                {
                    if (string.IsNullOrEmpty(storageSN))
                    {
                        rowSql = string.Format(@"SELECT TOP 10 it.[Storage_SN],it.[Area_SN],it.[Location_SN],it.[PartNumber],it.Qty,sa.Area_Order FROM [dbo].[wms_T_Inventory] it 
LEFT JOIN T_Bllb_StorageArea_tbsa sa ON it.Area_SN=sa.Area_SN
LEFT JOIN T_Bllb_Storage_tbs st ON it.Storage_SN=st.Storage_SN
LEFT JOIN wms_T_StorageRegion sr ON st.Region_SN = sr.Region_SN
WHERE sr.Region_Name='{0}' AND it.PartNumber='{1}' AND it.Status='1' and sa.Assigned_Type='Y'  ORDER BY it.DateCode,it.EnterTime,sa.Area_Order,sa.Area_SN,it.Location_SN", regionName, productNumber);
                    }
                    else
                    {
                        rowSql = string.Format(@"SELECT TOP 10 it.[Storage_SN],it.[Area_SN],it.[Location_SN],it.[PartNumber],it.Qty,sa.Area_Order FROM [dbo].[wms_T_Inventory] it 
LEFT JOIN T_Bllb_StorageArea_tbsa sa ON it.Area_SN=sa.Area_SN
LEFT JOIN T_Bllb_Storage_tbs st ON it.Storage_SN=st.Storage_SN
LEFT JOIN wms_T_StorageRegion sr ON st.Region_SN = sr.Region_SN
WHERE sr.Region_Name='{0}' AND it.PartNumber='{1}' AND it.Status='1' and sa.Assigned_Type='Y' and it.Storage_SN='{2}'  ORDER BY it.DateCode, it.EnterTime,sa.Area_Order,sa.Area_SN,it.Location_SN", regionName, productNumber, storageSN);
                    }
                }
                else
                {
                    if (string.IsNullOrEmpty(storageSN))
                    {
                        rowSql = string.Format(@"SELECT TOP 10 it.[Storage_SN],it.[Area_SN],it.[Location_SN],it.[PartNumber],it.Qty,sa.Area_Order FROM [dbo].[wms_T_Inventory] it 
LEFT JOIN T_Bllb_StorageArea_tbsa sa ON it.Area_SN=sa.Area_SN
LEFT JOIN T_Bllb_Storage_tbs st ON it.Storage_SN=st.Storage_SN
LEFT JOIN wms_T_StorageRegion sr ON st.Region_SN = sr.Region_SN
WHERE sr.Region_Name='{0}' AND it.PartNumber='{1}' AND it.Status='1' and sa.Assigned_Type='Y'
and  it.LotNo IN (SELECT DISTINCT LotNo FROM wms_R_LotSelect WHERE RelatedBill='{2}' AND PartNumber='{1}')
ORDER BY it.DateCode,it.EnterTime,sa.Area_Order,sa.Area_SN,it.Location_SN", regionName, productNumber, relatedBill);
                    }
                    else
                    {
                        rowSql = string.Format(@"SELECT TOP 10 it.[Storage_SN],it.[Area_SN],it.[Location_SN],it.[PartNumber],it.Qty,sa.Area_Order FROM [dbo].[wms_T_Inventory] it 
LEFT JOIN T_Bllb_StorageArea_tbsa sa ON it.Area_SN=sa.Area_SN
LEFT JOIN T_Bllb_Storage_tbs st ON it.Storage_SN=st.Storage_SN
LEFT JOIN wms_T_StorageRegion sr ON st.Region_SN = sr.Region_SN
WHERE sr.Region_Name='{0}' AND it.PartNumber='{1}' AND it.Status='1' and it.Storage_SN='{2}' and sa.Assigned_Type='Y'
and  it.LotNo IN (SELECT DISTINCT LotNo FROM wms_R_LotSelect WHERE RelatedBill='{3}' AND PartNumber='{1}')
ORDER BY it.DateCode,it.EnterTime,sa.Area_Order,sa.Area_SN,it.Location_SN", regionName, productNumber, storageSN, relatedBill);
                    }

                }

                DataTable rowDT = Utils.NMS.GetDataTable(rowSql);

                DataTable addRowDt = rowDT.Copy();

                if (rowDT.Rows.Count > 0)
                {
                    int realqty = 0;
                    if (!string.IsNullOrEmpty(row["SelectQty"].ToString()))
                    {
                        realqty = Convert.ToInt32(row["SelectQty"]);
                    }

                    foreach (DataRow rDR in rowDT.Rows)
                    {
                        var qty = 0;

                        if (!string.IsNullOrEmpty(rDR["Qty"].ToString()))
                        {
                            qty = Convert.ToInt32(rDR["Qty"]);
                        }

                        addRowDt.Rows.Add(rDR.ItemArray);


                        if (planQty <= (realqty + qty))
                        {
                            break;
                        }
                        realqty += qty;
                    }

                }
                dt.Merge(addRowDt);
            }
            return dt;
        }

        public static DataTable GetRecommendLocationNG(string regionName, string storageSN, string billNo)
        {
            string sql = string.Format(@"SELECT os.RelatedBill,sr.* FROM dbo.wms_T_outStock os
LEFT JOIN dbo.wms_T_OutStockRows sr ON os.BillNo=sr.BillNo
WHERE os.BillNo='{0}' and  ISNULL( sr.PlanQty,0)> ISNULL(sr.SelectQty,0)", billNo);
            var stockRowsDT = Utils.NMS.GetDataTable(sql);
            DataTable dt = new DataTable();
            foreach (DataRow row in stockRowsDT.Rows)
            {
                var relatedBill = row["RelatedBill"].ToString();
                var productNumber = row["ProductNumber"].ToString();
                var planQty = 0;
                if (!string.IsNullOrEmpty(row["PlanQty"].ToString()))
                {
                    planQty = Convert.ToInt32(row["PlanQty"]);
                }

                var rowNo = row["RowNo"].ToString();

                //                var deleteSql = string.Format(@"DELETE FROM [dbo].[wms_T_OutStockRowDetails] 
                //WHERE Status='0' AND BillNo='{0}' AND RowNo='{1}'", billNo, rowNo);
                //                Utils.NMS.ExcuteSQL(deleteSql);

                var lotSql = string.Format(@"SELECT * FROM wms_R_LotSelect WHERE RelatedBill='{0}' AND PartNumber='{1}'", relatedBill, productNumber);

                var lotSelect = Utils.NMS.GetDataTable(lotSql);

                var rowSql = string.Empty;
                if (lotSelect.Rows.Count == 0)
                {
                    if (string.IsNullOrEmpty(storageSN))
                    {
                        rowSql = string.Format(@"SELECT TOP 10 it.[Storage_SN],it.[Area_SN],it.[Location_SN],it.[PartNumber],it.Qty,sa.Area_Order,it.Version FROM [dbo].[wms_T_Inventory] it 
LEFT JOIN T_Bllb_StorageArea_tbsa sa ON it.Area_SN=sa.Area_SN
LEFT JOIN T_Bllb_Storage_tbs st ON it.Storage_SN=st.Storage_SN
LEFT JOIN wms_T_StorageRegion sr ON st.Region_SN = sr.Region_SN
WHERE sr.Region_Name='{0}' AND it.PartNumber='{1}' ORDER BY it.DateCode,it.EnterTime,sa.Area_Order,sa.Area_SN,it.Location_SN", regionName, productNumber);
                    }
                    else
                    {
                        rowSql = string.Format(@"SELECT TOP 10 it.[Storage_SN],it.[Area_SN],it.[Location_SN],it.[PartNumber],it.Qty,sa.Area_Order,it.Version FROM [dbo].[wms_T_Inventory] it 
LEFT JOIN T_Bllb_StorageArea_tbsa sa ON it.Area_SN=sa.Area_SN
LEFT JOIN T_Bllb_Storage_tbs st ON it.Storage_SN=st.Storage_SN
LEFT JOIN wms_T_StorageRegion sr ON st.Region_SN = sr.Region_SN
WHERE sr.Region_Name='{0}' AND it.PartNumber='{1}' and it.Storage_SN='{2}'  ORDER BY it.DateCode, it.EnterTime,sa.Area_Order,sa.Area_SN,it.Location_SN", regionName, productNumber, storageSN);
                    }
                }
                else
                {
                    if (string.IsNullOrEmpty(storageSN))
                    {
                        rowSql = string.Format(@"SELECT TOP 10 it.[Storage_SN],it.[Area_SN],it.[Location_SN],it.[PartNumber],it.Qty,sa.Area_Order,it.Version FROM [dbo].[wms_T_Inventory] it 
LEFT JOIN T_Bllb_StorageArea_tbsa sa ON it.Area_SN=sa.Area_SN
LEFT JOIN T_Bllb_Storage_tbs st ON it.Storage_SN=st.Storage_SN
LEFT JOIN wms_T_StorageRegion sr ON st.Region_SN = sr.Region_SN
WHERE sr.Region_Name='{0}' AND it.PartNumber='{1}' 
and  it.LotNo IN (SELECT DISTINCT LotNo FROM wms_R_LotSelect WHERE RelatedBill='{2}' AND PartNumber='{1}')
ORDER BY it.DateCode,it.EnterTime,sa.Area_Order,sa.Area_SN,it.Location_SN", regionName, productNumber, relatedBill);
                    }
                    else
                    {
                        rowSql = string.Format(@"SELECT TOP 10 it.[Storage_SN],it.[Area_SN],it.[Location_SN],it.[PartNumber],it.Qty,sa.Area_Order,it.Version FROM [dbo].[wms_T_Inventory] it 
LEFT JOIN T_Bllb_StorageArea_tbsa sa ON it.Area_SN=sa.Area_SN
LEFT JOIN T_Bllb_Storage_tbs st ON it.Storage_SN=st.Storage_SN
LEFT JOIN wms_T_StorageRegion sr ON st.Region_SN = sr.Region_SN
WHERE sr.Region_Name='{0}' AND it.PartNumber='{1}' and it.Storage_SN='{2}' 
and  it.LotNo IN (SELECT DISTINCT LotNo FROM wms_R_LotSelect WHERE RelatedBill='{3}' AND PartNumber='{1}')
ORDER BY it.DateCode,it.EnterTime,sa.Area_Order,sa.Area_SN,it.Location_SN", regionName, productNumber, storageSN, relatedBill);
                    }

                }

                DataTable rowDT = Utils.NMS.GetDataTable(rowSql);

                DataTable addRowDt = rowDT.Copy();

                if (rowDT.Rows.Count > 0)
                {
                    int realqty = 0;
                    if (!string.IsNullOrEmpty(row["SelectQty"].ToString()))
                    {
                        realqty = Convert.ToInt32(row["SelectQty"]);
                    }

                    foreach (DataRow rDR in rowDT.Rows)
                    {
                        var qty = 0;

                        if (!string.IsNullOrEmpty(rDR["Qty"].ToString()))
                        {
                            qty = Convert.ToInt32(rDR["Qty"]);
                        }

                        addRowDt.Rows.Add(rDR.ItemArray);


                        if (planQty <= (realqty + qty))
                        {
                            break;
                        }
                        realqty += qty;
                    }

                }
                dt.Merge(addRowDt);
            }
            return dt;
        }

        public static DataTable GetRecommendLocationSNQC(string regionName, string storageSN, string billNo)
        {
            string sql = string.Format(@"SELECT os.RelatedBill,sr.* FROM dbo.wms_T_outStock os
LEFT JOIN dbo.wms_T_OutStockRows sr ON os.BillNo=sr.BillNo
WHERE os.BillNo='{0}' and  ISNULL( sr.PlanQty,0)> ISNULL(sr.SelectQty,0)", billNo);
            var stockRowsDT = Utils.NMS.GetDataTable(sql);
            DataTable dt = new DataTable();
            foreach (DataRow row in stockRowsDT.Rows)
            {
                var relatedBill = row["RelatedBill"].ToString();
                var productNumber = row["ProductNumber"].ToString();
                var planQty = 0;
                if (!string.IsNullOrEmpty(row["PlanQty"].ToString()))
                {
                    planQty = Convert.ToInt32(row["PlanQty"]);
                }
                var rowNo = row["RowNo"].ToString();
                var rowSql = string.Empty;

                if (productNumber.Trim().StartsWith("TE") ||
                    productNumber.Trim().StartsWith("GM") ||
                    productNumber.Trim().StartsWith("FOD"))
                {
                    rowSql = string.Format(@"SELECT TOP 10 it.[Storage_SN],it.[Area_SN],it.[Location_SN],it.[PartNumber],it.Qty,sa.Area_Order,it.Version FROM [dbo].[wms_T_Inventory] it 
LEFT JOIN T_Bllb_StorageArea_tbsa sa ON it.Area_SN=sa.Area_SN
LEFT JOIN T_Bllb_Storage_tbs st ON it.Storage_SN=st.Storage_SN
LEFT JOIN wms_T_StorageRegion sr ON st.Region_SN = sr.Region_SN
WHERE sr.Region_Name='{0}' AND it.PartNumber='{1}' AND it.Status='1'  and it.Storage_SN='{2}'  ORDER BY it.DateCode, it.EnterTime,sa.Area_Order,sa.Area_SN,it.Location_SN", regionName, productNumber, storageSN);

                }
                else
                {
                    rowSql = string.Format(@"SELECT TOP 10 it.[Storage_SN],it.[Area_SN],it.[Location_SN],it.[PartNumber],it.Qty,sa.Area_Order,it.Version FROM [dbo].[wms_T_Inventory] it 
LEFT JOIN T_Bllb_StorageArea_tbsa sa ON it.Area_SN=sa.Area_SN
LEFT JOIN T_Bllb_Storage_tbs st ON it.Storage_SN=st.Storage_SN
LEFT JOIN wms_T_StorageRegion sr ON st.Region_SN = sr.Region_SN
WHERE sr.Region_Name='{0}' AND it.PartNumber='{1}' AND it.Status='1'  and it.Storage_SN='{2}'  ORDER BY it.DateCode, it.EnterTime,sa.Area_Order,sa.Area_SN,it.Location_SN", regionName, productNumber, storageSN);
                }

                DataTable rowDT = Utils.NMS.GetDataTable(rowSql);
                DataTable addRowDt = rowDT.Copy();

                if (rowDT.Rows.Count > 0)
                {
                    int realqty = 0;
                    if (!string.IsNullOrEmpty(row["SelectQty"].ToString()))
                    {
                        realqty = Convert.ToInt32(row["SelectQty"]);
                    }

                    foreach (DataRow rDR in rowDT.Rows)
                    {
                        var qty = 0;

                        if (!string.IsNullOrEmpty(rDR["Qty"].ToString()))
                        {
                            qty = Convert.ToInt32(rDR["Qty"]);
                        }

                        addRowDt.Rows.Add(rDR.ItemArray);


                        if (planQty <= (realqty + qty))
                        {
                            break;
                        }
                        realqty += qty;
                    }

                }
                dt.Merge(addRowDt);
            }
            return dt;
        }

        public static DataTable GetRecommendDetails(string billNo)
        {
            string sql = string.Format(@"SELECT * FROM [dbo].[wms_T_OutStockRowDetails]
WHERE Status=0 AND BillNo='{0}' ORDER BY Area_SN,Location_SN", billNo);
            var dt = Utils.NMS.GetDataTable(sql);
            return dt;
        }

        public static DataTable GetOutStockInfo(string billNo)
        {
            var sql = string.Format(@"SELECT * FROM [dbo].[wms_T_OutStock] WHERE BillNo='{0}'", billNo);

            var dt = Utils.NMS.GetDataTable(sql);

            return dt;
        }

        //        public static DataTable GetOutStockRowsByProductNumber(string billNo, string boxNo)
        //        {
        //            string sql = string.Format(@"SELECT * FROM [dbo].[wms_T_InStockRowDetails]
        //WHERE BoxNo='{0}' AND PartNumber IN (SELECT ProductNumber FROM [dbo].[wms_T_OutStockRows] WHERE BillNo='{1}')", boxNo, billNo);
        ////            string sql = string.Format(@"SELECT * FROM [dbo].[wms_T_Inventory]
        ////WHERE BoxNo='{0}' AND PartNumber IN (SELECT ProductNumber FROM [dbo].[wms_T_OutStockRows] WHERE BillNo='{1}')", boxNo, billNo);
        //            var result = Utils.NMS.GetDataTable(sql);
        //            return result;
        //        }

        public static DataTable GetBoxNoInfo(string boxNo)
        {
            string sql = string.Format(@"SELECT * FROM dbo.wms_T_InStockRowDetails  WHERE BoxNo='{0}' and status<>'H' ORDER BY Status", boxNo);

            var dt = Utils.NMS.GetDataTable(sql);

            return dt;
        }

        public static DataTable GetOutStockRowsDT(string billNo)
        {
            var sql = string.Format(@"SELECT * FROM  dbo.wms_T_OutStockRows  WHERE BillNo='{0}'", billNo);

            var result = Utils.NMS.GetDataTable(sql);

            return result;
        }

        public static DataTable GetExpriTime(string partNumber, string Location_SN)
        {
            string sqlcmd = string.Format(@"select *,DATEDIFF(day,GETDATE(),c.D2) as Diff from (
select Ity.Location_SN,Ity.DateCode,Ity.Qty, Mtl.ShelfLifeTime
,
															convert(datetime,SUBSTRING(ISNULL(Ity.DateCode,'19000101'),1,4) + '-' + 
																						   SUBSTRING(ISNULL(Ity.DateCode,'19000101'),5,2) + '-' + 
																							 SUBSTRING(ISNULL(Ity.DateCode,'19000101'),7,2)) as D1
,
dateadd(day,Mtl.ShelfLifeTime,convert(datetime,SUBSTRING(ISNULL(Ity.DateCode,'19000101'),1,4) + '-' + 
																						   SUBSTRING(ISNULL(Ity.DateCode,'19000101'),5,2) + '-' + 
																							 SUBSTRING(ISNULL(Ity.DateCode,'19000101'),7,2))) as D2
from wms_T_Inventory Ity inner join 
MdcdatMaterial Mtl on Ity.PartNumber = Mtl.MaterialCode
where  len(DateCode)=8 and Ity.PartNumber='{0}' and Ity.Location_SN='{1}'  ) c  where DATEDIFF(day,GETDATE(),c.D2)>=0", partNumber, Location_SN);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }

        public static bool OutStockIsCanAddQty(string billNo, string productNumber, decimal qty)
        {
            var sql = string.Format(@"SELECT * FROM dbo.wms_T_OutStockRows WHERE ProductNumber='{0}' AND ISNULL(PlanQty,0)>= ISNULL(SelectQty,0)+{1} AND BillNo='{2}'", productNumber, qty, billNo);

            var dt = Utils.NMS.GetDataTable(sql);
            var result = false;
            if (dt != null && dt.Rows.Count > 0)
            {
                result = true;
            }
            return result;
        }


        public static bool ProductOutStock(string billNo, string boxNo, int qty, string locationSN, string partNumber)
        {
            var result = false;

            try
            {

                var inventorySql = string.Format(@"SELECT * FROM [wms_T_Inventory] WHERE Location_SN='{0}' and  PartNumber='{1}'", locationSN, partNumber);

                var inventoryInfo = Utils.NMS.GetDataTable(inventorySql);

                if (inventoryInfo.Rows.Count == 0)
                {
                    return false;
                }

                var inv_Qty = Convert.ToInt32(inventoryInfo.Rows[0]["Qty"]);

                if (inv_Qty < qty)
                {
                    return false;
                }

                string productSearch_Sql = string.Format(@"SELECT sr.*,os.RelatedBill,os.Ext1 as TargetStorage FROM dbo.wms_T_OutStockRows sr
LEFT JOIN dbo.wms_T_OutStock os ON sr.BillNo=os.BillNo
WHERE  sr.BillNo='{0}' AND sr.ProductNumber='{1}'", billNo, partNumber);

                DataTable dt = Utils.NMS.GetDataTable(productSearch_Sql);

                if (dt.Rows.Count == 0)
                {
                    return false;
                }
                var row = dt.Rows[0];

                var storageSN = inventoryInfo.Rows[0]["Storage_SN"].ToString();
                var areaSN = inventoryInfo.Rows[0]["Area_SN"].ToString();
                var rowNo = row["RowNo"].ToString();
                var relatedBill = row["RelatedBill"].ToString();
                var targetStorage = row["TargetStorage"].ToString();
                var version = inventoryInfo.Rows[0]["Version"].ToString();
                var dateCode = inventoryInfo.Rows[0]["DateCode"].ToString();
                var lotNo = inventoryInfo.Rows[0]["LotNo"].ToString();
                var unit = inventoryInfo.Rows[0]["Unit"].ToString();


                string sql = string.Format(@"INSERT INTO [dbo].[wms_T_OutStockRowDetails]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[BillNo]
           ,[RowNo]
           ,[PartNumber]
           ,[Version]
           ,[DateCode]
           ,[LotNo]
           ,[BoxNo]
           ,[Creator]
           ,[CreateTime]
           ,[Qty]
           ,[Status]
           ,[Unit])
		   VALUES
           (NEWID()
      ,'{0}'
      ,'{1}'
      ,'{2}'
	  ,'{3}'
	  ,'{4}'
      ,'{5}'
      ,'{6}'
      ,'{7}'
	  ,'{8}'
	  ,'{9}'
	  ,'{10}'
      ,GETDATE()
      ,'{11}'
      ,'1'
      ,'{12}');", storageSN, areaSN, locationSN, billNo, rowNo, partNumber, version, dateCode, lotNo, boxNo,
               Utils.uContext.UserNamek__BackingField, qty, unit);

                //var sqlcmdRes = Utils.NMS.ExcuteSQL(sqlcmd);



                string sql2 = string.Format(@"UPDATE [dbo].[wms_T_OutStockRows]
   SET  [SelectQty] = (SELECT SUM([Qty]) FROM [wms_T_OutStockRowDetails] WHERE BillNo='{0}' AND RowNo='{1}' AND status='1')
 WHERE  BillNo='{0}' AND RowNo='{1}';
UPDATE dbo.wms_T_OutStock SET Updator='{2}',UpdateTime=GETDATE() WHERE BillNo='{0}';", billNo, rowNo, Utils.uContext.UserNamek__BackingField);

                //var sqlcmd2_result = Utils.NMS.ExcuteSQL(sqlcmd2);


                string sql3 = string.Empty;
                string sql4 = string.Empty;
                if (inv_Qty == qty)
                {
                    sql3 = string.Format(@"delete  FROM [dbo].[wms_T_Inventory] WHERE  Location_SN='{0}' and PartNumber='{1}';", locationSN, partNumber);
                    sql4 = string.Format(@"UPDATE  T_Bllb_StorageLocation_tbsl SET Status_Flag='0' WHERE Location_SN='{0}';", locationSN);

                    //Utils.NMS.ExcuteSQL(update_loc);

                }
                else if (inv_Qty > qty)
                {
                    sql3 = string.Format(@"UPDATE [wms_T_Inventory] SET Qty=Qty-{0} WHERE Location_SN='{1}' and PartNumber='{2}';", qty, locationSN, partNumber);

                }

                //Utils.NMS.ExcuteSQL(update_inv);

                var sql5 = string.Format(@"UPDATE  dbo.wms_T_InStockRowDetails SET  RealQty=RealQty-{0} WHERE BoxNo='{1}' and (status='0' or status='9');", qty, boxNo);
                //Utils.NMS.ExcuteSQL(boxSql);

                string sql6 = string.Format(@"INSERT INTO [dbo].[wms_T_InventoryHistory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Qty]
           ,[Creator]
           ,[CreateTime]
           ,[IO]
           ,[BoxNo]
           ,[LotNo],RelatedBill1,RelatedBill2)
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,GETDATE()
           ,'{7}'
           ,'{8}'
           ,'{9}','{10}','{11}');", Guid.NewGuid(), storageSN, areaSN, locationSN, partNumber,
                        "-" + qty, Utils.uContext.UserNamek__BackingField, "O", boxNo, lotNo, relatedBill, billNo);

                //Utils.NMS.ExcuteSQL(insertHis_Sql);
                //***DEL 20191016 START
                var sql7 = string.Empty;
                var sql8 = string.Empty;
                //                if (relatedBill.StartsWith("VMI"))
                //                {
                //                    var sql_Ser_JS = string.Format(@"SELECT * FROM dbo.wms_T_Inventory WHERE Storage_SN='{0}' AND PartNumber='{1}'", targetStorage, partNumber);

                //                    var serDt = Utils.NMS.GetDataTable(sql_Ser_JS);

                //                    if (serDt.Rows.Count == 0)
                //                    {
                //                        sql7 = string.Format(@"INSERT INTO [dbo].[wms_T_Inventory]
                //           ([Guid]
                //           ,[Storage_SN]
                //           ,[Area_SN]
                //           ,[Location_SN]
                //           ,[PartNumber]
                //           ,[Qty]
                //           ,[EnterTime]
                //           ,[Status]
                //           ,[Unit])
                //SELECT NEWID() AS guid,ins.Storage_SN,ins.Area_SN,ins.Location_SN,ins.PartNumber,ins.Qty+ISNULL(inv.Qty,0) AS Qty,ins.EnterTime,ins.Status,ins.Unit FROM (
                //SELECT os.Ext1 AS Storage_SN,'0' AS Area_SN,'0' AS Location_SN,sr.PartNumber AS PartNumber,sr.Qty AS Qty,GETDATE() AS EnterTime,'1' AS Status,sr.Unit AS Unit FROM dbo.wms_T_OutStock os
                //LEFT JOIN dbo.wms_T_OutStockRowDetails sr ON os.BillNo=sr.BillNo
                //WHERE os.BillNo='{0}' AND sr.Status='1'and sr.BoxNo='{1}'
                //) ins
                //LEFT JOIN  wms_T_Inventory inv ON ins.Storage_SN=inv.Storage_SN AND ins.PartNumber=inv.PartNumber;", billNo, boxNo);
                //                    }
                //                    else
                //                    {
                //                        sql7 = string.Format(@"UPDATE dbo.wms_T_Inventory SET Qty=Qty+{0} WHERE Guid='{1}';", qty, serDt.Rows[0]["Guid"].ToString());
                //                    }

                //                sql8 = string.Format(@"INSERT INTO [dbo].[wms_T_InventoryHistory]
                //                                           ([Guid]
                //                                           ,[Storage_SN]
                //                                           ,[Area_SN]
                //                                           ,[Location_SN]
                //                                           ,[PartNumber]
                //                                           ,[Qty]
                //                                           ,[Creator]
                //                                           ,[CreateTime]
                //                                           ,[IO]
                //                                           ,[BoxNo]
                //                                           ,[LotNo],RelatedBill1,RelatedBill2)
                //                                     VALUES
                //                                           ('{0}'
                //                                           ,'{1}'
                //                                           ,'{2}'
                //                                           ,'{3}'
                //                                           ,'{4}'
                //                                           ,'{5}'
                //                                           ,'{6}'
                //                                           ,GETDATE()
                //                                           ,'{7}'
                //                                           ,'{8}'
                //                                           ,'{9}','{10}','{11}');", Guid.NewGuid(), targetStorage, "0", "0", partNumber,
                //                     qty, Utils.uContext.UserNamek__BackingField, "I", boxNo, lotNo, relatedBill, billNo);

                //}
                //***DEL 20191016 END
                var sqlCmd = (sql + sql2 + sql3 + sql4 + sql5 + sql6 + sql7 + sql8).Trim(';');


                sqlCmd = @"---开启事务
                begin tran
                begin try  
                " + sqlCmd
 + @"
                end try
                begin catch
                   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
                      rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
                end catch
                if(@@trancount>0)
                commit tran";

                var excRes = Utils.NMS.ExcuteSQL(sqlCmd);

                if (excRes > 0)
                {
                    result = true;
                }

            }
            catch (Exception ex)
            {

            }
            return result;
        }

        public static bool CheckOutStockAndUpdate(string billNo)
        {
            var sql = string.Format(@"SELECT * FROM [dbo].[wms_T_OutStockRows]
WHERE BillNo='{0}' AND PlanQty>SelectQty", billNo);
            var dt = Utils.NMS.GetDataTable(sql);
            var result = false;
            if (dt.Rows.Count == 0)
            {
                string updateSql = string.Format(@"UPDATE [dbo].[wms_T_OutStock]
               SET [Status] = '1'
                  ,[Updator] = '{0}'
                  ,[UpdateTime] = '{1}'
             WHERE BillNo='{2}'", Utils.uContext.UserNamek__BackingField, DateTime.Now, billNo);

                string updateRowsSql = string.Format(@"UPDATE dbo.wms_T_OutStockRows SET RealQty=SelectQty WHERE BillNo='{0}'", billNo);

                Utils.NMS.ExcuteSQL(updateRowsSql);

                Utils.NMS.ExcuteSQL(updateSql);
                result = true;
            }
            return result;
        }

        public static string GetRelationBill(string billNo)
        {
            var result = string.Empty;
            string selectSql = string.Format(@"select * from [dbo].[wms_T_OutStock] where billNo='{0}'", billNo);

            DataTable dt = Utils.NMS.GetDataTable(selectSql);

            if (dt.Rows.Count > 0)
            {
                result = dt.Rows[0]["RelatedBill"].ToString();
            }
            return result;

        }

        public static bool OutStockDeliveryConfirm(string billNo, out string relatedBill)
        {
            var result = false;
            relatedBill = string.Empty;

            try
            {
                string selectSql = string.Format(@"select * from [dbo].[wms_T_OutStock] where billNo='{0}'", billNo);

                DataTable dt = Utils.NMS.GetDataTable(selectSql);

                if (dt.Rows.Count > 0)
                {
                    relatedBill = dt.Rows[0]["RelatedBill"].ToString();
                }


                string updateSql = string.Format(@"UPDATE [dbo].[wms_T_OutStock]
               SET [Status] = '1'
                  ,[Updator] = '{0}'
                  ,[UpdateTime] = GETDATE()
             WHERE BillNo='{1}';", Utils.uContext.UserNamek__BackingField, billNo);

                string updateRowsSql = string.Format(@"UPDATE dbo.wms_T_OutStockRows SET RealQty=SelectQty WHERE BillNo='{0}';", billNo);

                var sqlCmd = (updateRowsSql + updateSql).TrimEnd(';');

                sqlCmd = @"---开启事务
                begin tran
                begin try  
                " + sqlCmd
+ @"
                end try
                begin catch
                   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
                      rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
                end catch
                if(@@trancount>0)
                commit tran";

                var excuRes = Utils.NMS.ExcuteSQL(sqlCmd);

                if (excuRes > 0)
                {
                    result = true;
                }
            }
            catch (Exception ex)
            {

            }
            return result;
        }

        public static DataTable GetDeliveryInfo(string billNo)
        {
            var sql = string.Format(@"SELECT os.CustomerName,sr.ProductNumber,sr.PlanQty,sr.SelectQty,os.Ext1 AS TargetStorage FROM dbo.wms_T_OutStock os
LEFT JOIN dbo.wms_T_OutStockRows sr ON os.BillNo=sr.BillNo
WHERE os.BillNo='{0}'", billNo);
            var result = Utils.NMS.GetDataTable(sql);
            return result;
        }

        public static int GetOutStockBoxsCount(string billNo)
        {
            var sql = string.Format(@"SELECT count(1) as Count FROM dbo.wms_T_OutStockRowDetails WHERE BillNo='{0}'", billNo);
            var dt = Utils.NMS.GetDataTable(sql);

            var result = 0;
            try
            {

                result = Convert.ToInt32(dt.Rows[0]["Count"]);
            }
            catch
            {

            }
            return result;
        }

        public static bool OutStockVMIDelivery(string billNo)
        {
            var result = false;
            try
            {
                var sql = string.Format(@"INSERT INTO [dbo].[wms_T_Inventory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Qty]
           ,[EnterTime]
           ,[Status]
           ,[Unit])
SELECT NEWID() AS guid,ins.Storage_SN,ins.Area_SN,ins.Location_SN,ins.PartNumber,ins.Qty+ISNULL(inv.Qty,0) AS Qty,ins.EnterTime,ins.Status,ins.Unit FROM (
SELECT os.Ext1 AS Storage_SN,'0' AS Area_SN,'0' AS Location_SN,sr.ProductNumber AS PartNumber,sr.SelectQty AS Qty,GETDATE() AS EnterTime,'1' AS Status,sr.Package AS Unit FROM dbo.wms_T_OutStock os
LEFT JOIN dbo.wms_T_OutStockRows sr ON os.BillNo=sr.BillNo
WHERE os.BillNo='{0}') ins
LEFT JOIN  wms_T_Inventory inv ON ins.Storage_SN=inv.Storage_SN AND ins.PartNumber=inv.PartNumber;", billNo);

                var sql2 = string.Format(@"INSERT INTO [dbo].[wms_T_InventoryHistory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Qty]
           ,[Creator]
           ,[CreateTime]
           ,[IO])
SELECT NEWID() AS guid,ins.Storage_SN,ins.Area_SN,ins.Location_SN,ins.PartNumber,ins.Qty+ISNULL(inv.Qty,0) AS Qty,'{0}' as creator,GETDATE() as createtime,'O' as io FROM (
SELECT os.Ext1 AS Storage_SN,'0' AS Area_SN,'0' AS Location_SN,sr.ProductNumber AS PartNumber,sr.SelectQty AS Qty,sr.Package AS Unit FROM dbo.wms_T_OutStock os
LEFT JOIN dbo.wms_T_OutStockRows sr ON os.BillNo=sr.BillNo
WHERE os.BillNo='{1}') ins
LEFT JOIN  wms_T_Inventory inv ON ins.Storage_SN=inv.Storage_SN AND ins.PartNumber=inv.PartNumber;", Utils.uContext.UserNamek__BackingField, billNo);

                var sqlCmd = (sql + sql2).TrimEnd(';');

                sqlCmd = @"---开启事务
                begin tran
                begin try  
                " + sqlCmd
+ @"
                end try
                begin catch
                   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
                      rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
                end catch
                if(@@trancount>0)
                commit tran";

                var excuRes = Utils.NMS.ExcuteSQL(sqlCmd);

                if (excuRes > 0)
                {
                    result = true;
                }

            }
            catch (Exception ex)
            {
            }
            return result;
        }

        public static DataTable GetOutStockRowDetailsInfo(string billNo, string boxNo)
        {
            var sql = string.Format(@"SELECT *,os.RelatedBill,os.Ext1 AS TargetStorage FROM dbo.wms_T_OutStockRowDetails  sr
left JOIN wms_t_OutStock os ON sr.billno=os.billno
WHERE sr.BillNo='{0}' AND sr.BoxNo='{1}'", billNo, boxNo);

            var result = Utils.NMS.GetDataTable(sql);

            return result;
        }

        public static bool BackOutStockRowDetails(DataTable dt, string storageSN, string areaSN, string locationSN, int status)
        {
            var result = false;
            try
            {
                var detail = dt.Rows[0];
                var billNo = detail["BillNo"].ToString();
                var rowNo = detail["RowNo"].ToString();
                var guid = detail["Guid"].ToString();
                var boxNo = detail["BoxNo"].ToString();
                var qty = Convert.ToDecimal(detail["Qty"]);
                var relatedBill = detail["RelatedBill"].ToString();
                var targetStorage = detail["TargetStorage"].ToString();
                string sql3 = string.Empty;
                if (status == 0)
                {
                    sql3 = string.Format(@"INSERT INTO [dbo].[wms_T_Inventory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Version]
           ,[DateCode]
           ,[Qty]
           ,[EnterTime]
           ,[Status]
           ,[LotNo]
           ,[Unit]
           ,[BoxNo])
SELECT NEWID(),'{1}','{2}','{3}',PartNumber,Version,DateCode,Qty,GETDATE(),'1',LotNo,Unit,BoxNo FROM dbo.wms_T_OutStockRowDetails WHERE Guid='{0}';", guid, storageSN, areaSN, locationSN);
                    sql3 = sql3 + string.Format(@"UPDATE  T_Bllb_StorageLocation_tbsl SET Status_Flag='1' WHERE Location_SN='{0}';", locationSN);
                }
                else
                {
                    sql3 = string.Format(@"update [dbo].[wms_T_Inventory] set [Qty]=[Qty]+{1} WHERE Location_SN='{0}' AND PartNumber='{2}';", locationSN, qty, detail["PartNumber"].ToString());
                }
                string sql = string.Format(@"DELETE FROM wms_T_OutStockRowDetails  WHERE Guid='{0}';", guid);
                string sql2 = string.Format(@"UPDATE [dbo].[wms_T_OutStockRows]
   SET  [SelectQty] = (SELECT SUM([Qty]) FROM [wms_T_OutStockRowDetails] WHERE BillNo='{0}' AND RowNo='{1}' AND status='1')
 WHERE  BillNo='{0}' AND RowNo='{1}';", billNo, rowNo);

                string sql4 = string.Format(@"UPDATE  dbo.wms_T_InStockRowDetails SET  status='0',RealQty= ISNULL(RealQty,0)+{0} WHERE BoxNo='{1}'; ", qty, boxNo);

                string insertHis_Sql = string.Format(@"INSERT INTO [dbo].[wms_T_InventoryHistory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Qty]
           ,[Creator]
           ,[CreateTime]
           ,[IO]
           ,[BoxNo]
           ,[LotNo],RelatedBill1,RelatedBill2)
SELECT NEWID(),'{1}','{2}','{3}',PartNumber,Qty,'{4}',GETDATE(),'I',BoxNo,LotNo,'{5}','{6}' FROM dbo.wms_T_OutStockRowDetails WHERE Guid='{0}';", guid, storageSN, areaSN, locationSN, Utils.uContext.UserNamek__BackingField, relatedBill, billNo);


                var sqlCmd_js = string.Empty;

                if (relatedBill.StartsWith("VMI"))
                {
                    var sql_ser_js = string.Format(@"SELECT * FROM dbo.wms_T_Inventory WHERE Storage_SN='{0}' AND PartNumber='{1}'", targetStorage, detail["PartNumber"].ToString());

                    var serDt = Utils.NMS.GetDataTable(sql_ser_js);

                    var sql_js = string.Empty;
                    if (serDt.Rows.Count > 0)
                    {
                        var serQty = string.IsNullOrEmpty(serDt.Rows[0]["Qty"].ToString()) ? 0 : Convert.ToInt32(serDt.Rows[0]["Qty"]);

                        if (serQty > qty)
                        {
                            sql_js = string.Format(@"UPDATE  wms_T_Inventory SET Qty=Qty-{1} WHERE Guid='{0}';", serDt.Rows[0]["Guid"].ToString(), qty);
                        }
                        else if (serQty == qty)
                        {
                            sql_js = string.Format(@"DELETE FROM wms_T_Inventory WHERE Guid='{0}';", serDt.Rows[0]["Guid"].ToString());
                        }
                    }

                    var sql_his_js = string.Format(@"INSERT INTO [dbo].[wms_T_InventoryHistory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Qty]
           ,[Creator]
           ,[CreateTime]
           ,[IO]
           ,[BoxNo]
           ,[LotNo],RelatedBill1,RelatedBill2)
SELECT NEWID(),'{1}','{2}','{3}',PartNumber,-Qty,'{4}',GETDATE(),'O',BoxNo,LotNo,'{5}','{6}' FROM dbo.wms_T_OutStockRowDetails WHERE Guid='{0}';", guid, targetStorage, "0", "0", Utils.uContext.UserNamek__BackingField, relatedBill, billNo);

                    sqlCmd_js = sql_js + sql_his_js;
                }

                var sqlCmd = (sql3 + insertHis_Sql + sql + sql2 + sql4 + sqlCmd_js).Trim(';');

                sqlCmd = @"---开启事务
                begin tran
                begin try  
                " + sqlCmd
+ @"
                end try
                begin catch
                   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
                      rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
                end catch
                if(@@trancount>0)
                commit tran";

                var excuRes = Utils.NMS.ExcuteSQL(sqlCmd);

                if (excuRes > 0)
                {
                    result = true;
                }
            }
            catch (Exception ex)
            {

            }
            return result;
        }


        public static bool OutStockFinish(string billNo)
        {
            var result = false;
            try
            {
                string updateSql = string.Format(@"UPDATE [dbo].[wms_T_OutStock]
               SET [Status] = '1'
                  ,[Updator] = '{0}'
                  ,[UpdateTime] = GETDATE()
             WHERE BillNo='{1}';
UPDATE dbo.wms_T_OutStockRows SET RealQty=SelectQty WHERE BillNo='{1}'", Utils.uContext.UserNamek__BackingField, billNo);
                Utils.NMS.ExcuteSQL(updateSql);
                result = true;
            }
            catch (Exception ex)
            {
            }
            return result;
        }
        public static int GetLocationSNStatusInInventory(string locationSN)
        {
            var result = 0;
            var sql = string.Format("SELECT * FROM wms_T_Inventory WHERE Location_SN='{0}';", locationSN);

            var dt = Utils.NMS.GetDataTable(sql);

            if (dt.Rows.Count > 0)
            {
                result = 1;
                if (dt.Rows[0]["Status"].ToString() == "H")
                {
                    result = -1;
                }
            }
            return result;
        }
        public static int GetLocationSNStatusInInventory(string locationSN, string PartNumber)
        {
            var result = 0;
            var sql = string.Format("SELECT * FROM wms_T_Inventory WHERE Location_SN='{0}' and  PartNumber='{1}';", locationSN, PartNumber);

            var dt = Utils.NMS.GetDataTable(sql);

            if (dt.Rows.Count > 0)
            {
                result = 1;
                if (dt.Rows[0]["Status"].ToString() == "H")
                {
                    result = -1;
                }
            }
            return result;
        }

        public static DataTable GetNGStorage()
        {
            string sql = string.Format(@" select  c.* FROM T_Bllb_Storage_tbs c 
 LEFT JOIN wms_T_StorageRegion d ON c.Region_SN=d.Region_SN
 WHERE  c.Storage_Type='202' and d.Region_Name='{0}'", Utils.uContext.CompanyNamek__BackingField);

            var result = Utils.NMS.GetDataTable(sql);

            return result;
        }

        public static bool SalesBack(string billNo, string productNumber, string Storage_SN, string Area_SN, string Location_SN)
        {

            string row_Sql = string.Format(@"SELECT sr.*,ot.RelatedBill,ot.Ext1,ot.Ext2,ot.Ext3  FROM  wms_T_OutStockRows sr
LEFT JOIN dbo.wms_T_OutStock ot ON sr.BillNo=ot.BillNo
WHERE sr.BillNo='{0}' AND sr.ProductNumber='{1}'", billNo, productNumber);

            DataTable dt = Utils.NMS.GetDataTable(row_Sql);

            if (dt.Rows.Count == 0)
            {
                return false;
            }
            var result = false;
            try
            {

                var row = dt.Rows[0];
                var RowNo = row["RowNo"].ToString();
                //var version = row["Ext1"].ToString();
                //var datecode = row["Ext2"].ToString();
                var version = "";
                var datecode = "";
                if (!string.IsNullOrEmpty(datecode))
                {
                    datecode = DateTime.Now.ToString("yyyyMMdd");
                }
                var lotno = row["Ext3"].ToString();
                if (string.IsNullOrEmpty(lotno))
                {
                    lotno = datecode;
                }
                var qty = -Convert.ToInt32(row["PlanQty"]);

                string sql = string.Format(@"UPDATE dbo.wms_T_OutStockRows SET SelectQty=-PlanQty,RealQty=-PlanQty  WHERE BillNo='{0}' AND ProductNumber='{2}';
UPDATE dbo.wms_T_OutStock SET Updator='{1}',UpdateTime=GETDATE() WHERE BillNo='{0}';", billNo, Utils.uContext.UserNamek__BackingField, productNumber);


                string insert_Sql = string.Format(@"INSERT INTO [dbo].[wms_T_Inventory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Qty]
           ,[EnterTime]
           ,[Status],Version,DateCode,LotNo)
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,GETDATE()
           ,'{6}','{7}','{8}','{9}');", Guid.NewGuid(), Storage_SN, Area_SN, Location_SN, productNumber, qty, "1", version, datecode, lotno);

                string insertDetails = string.Format(@"INSERT INTO [dbo].[wms_T_OutStockRowDetails]
            (Guid
            ,Storage_SN
            ,Area_SN
            ,Location_SN
            ,BillNo
            ,RowNo
            ,PartNumber
            ,Creator
            ,CreateTime
            ,Qty
            ,Status
            ,DateCode
            )
            VALUES
            ('{0}'
            ,'{1}'
            ,'{2}'
            ,'{3}'
            ,'{4}'
            ,'{5}'
            ,'{6}'
            ,'{7}'
            ,GETDATE()
            ,{8}
            ,1
            ,'{9}'
            );", Guid.NewGuid(), Storage_SN, Area_SN, Location_SN, billNo, RowNo, productNumber, Utils.uContext.UserNamek__BackingField, qty, datecode);

                string insertHis_Sql = string.Format(@"INSERT INTO [dbo].[wms_T_InventoryHistory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Qty]
           ,[Creator]
           ,[CreateTime]
           ,[IO],RelatedBill1,RelatedBill2,LotNo)
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,GETDATE()
           ,'{7}','{8}','{9}','{10}');", Guid.NewGuid(), Storage_SN, Area_SN, Location_SN, productNumber,
                        qty, Utils.uContext.UserNamek__BackingField, "I", row["RelatedBill"].ToString(), billNo, lotno);


                var updateStatusSql = string.Format(@"UPDATE wms_T_OutStock SET Status='1' WHERE BillNo='{0}'
 AND NOT EXISTS (SELECT * FROM wms_T_OutStockRows WHERE PlanQty<>-SelectQty AND BillNo='{0}');", billNo);

                var updateLocationStatusSql = string.Format(@"UPDATE T_Bllb_StorageLocation_tbsl SET Status_Flag='1' WHERE Location_SN='{0}';", Location_SN);

                var sqlCmd = (sql + insert_Sql + insertHis_Sql + updateStatusSql + insertDetails + updateLocationStatusSql).TrimEnd(';');


                sqlCmd = @"---开启事务
                begin tran
                begin try  
                " + sqlCmd
+ @"
                end try
                begin catch
                   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
                      rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
                end catch
                if(@@trancount>0)
                commit tran";

                var excuRes = Utils.NMS.ExcuteSQL(sqlCmd);

                if (excuRes > 0)
                {
                    result = true;
                }


            }
            catch (Exception ex)
            {
            }
            return result;
        }

        public static bool IsExitBill(string billNo, string boxNo)
        {
            string sql = string.Format(@"SELECT * FROM dbo.wms_T_OutStockRowDetails WHERE BillNo='{0}' AND BoxNo='{1}'", billNo, boxNo);

            var result = false;

            var dt = Utils.NMS.GetDataTable(sql);

            if (dt.Rows.Count > 0)
            {
                result = true;
            }
            return result;
        }



        public static bool BackOutStockRowDetailsEndInventory(DataTable dt, string storageSN, string areaSN, string locationSN, string endstorageSN, string endareaSN, string endlocationSN, string NewBoxNo)
        {
            var result = false;
            try
            {
                var detail = dt.Rows[0];
                var billNo = detail["BillNo"].ToString();
                var rowNo = detail["RowNo"].ToString();
                var guid = detail["Guid"].ToString();
                var boxNo = detail["BoxNo"].ToString();
                var qty = Convert.ToInt32(detail["Qty"]);
                var relatedBill = detail["RelatedBill"].ToString();
                var targetStorage = detail["TargetStorage"].ToString();

                //string sql3 = string.Format(@"update [wms_T_Inventory] set Qty=Qty+{0} where Location_SN='{1}';", qty, endlocationSN);
                string sql = string.Format(@"update [wms_T_OutStockRowDetails]  set BoxNo='{0}'  WHERE Guid='{1}';", NewBoxNo, guid);
                string sql2 = string.Format(@"UPDATE  dbo.wms_T_InStockRowDetails SET  RealQty= ISNULL(RealQty,0)-{0} WHERE BoxNo='{1}' and status='0'; ", qty, NewBoxNo);
                string sql4 = string.Format(@"UPDATE  dbo.wms_T_InStockRowDetails SET  RealQty= ISNULL(RealQty,0)+{0} WHERE BoxNo='{1}' and status='0'; ", qty, boxNo);
                //var sql_ser_js = string.Format(@"SELECT * FROM dbo.wms_T_Inventory WHERE location_sn='{0}'", locationSN);
                //var serDt = Utils.NMS.GetDataTable(sql_ser_js);
                //string sql5 = string.Empty;
                //if (serDt.Rows.Count > 0)
                //{
                //    var serQty = string.IsNullOrEmpty(serDt.Rows[0]["Qty"].ToString()) ? 0 : Int32.Parse(serDt.Rows[0]["Qty"].ToString());

                //    if (serQty > qty)
                //    {
                //        sql5 = string.Format(@"UPDATE  wms_T_Inventory SET Qty=Qty-{1} WHERE Guid='{0}';", serDt.Rows[0]["Guid"].ToString(), qty);
                //    }
                //    else if (serQty == qty)
                //    {
                //        sql5 = string.Format(@"DELETE FROM wms_T_Inventory WHERE Guid='{0}';", serDt.Rows[0]["Guid"].ToString());
                //        sql4 = string.Format(@"UPDATE  T_Bllb_StorageLocation_tbsl SET Status_Flag='0' WHERE Location_SN='{0}';", locationSN);
                //    }
                //}
                string insertHis_Sql = string.Format(@"INSERT INTO [dbo].[wms_T_InventoryHistory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Qty]
           ,[Creator]
           ,[CreateTime]
           ,[IO]
           ,[BoxNo]
           ,[LotNo],RelatedBill1,RelatedBill2)
SELECT NEWID(),'{1}','{2}','{3}',PartNumber,Qty,'{4}',GETDATE(),'I',BoxNo,LotNo,'{5}','{6}' FROM dbo.wms_T_OutStockRowDetails WHERE Guid='{0}';", guid, storageSN, areaSN, locationSN, Utils.uContext.UserNamek__BackingField, relatedBill, billNo);
                string OutHis_Sql = string.Format(@"INSERT INTO [dbo].[wms_T_InventoryHistory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Qty]
           ,[Creator]
           ,[CreateTime]
           ,[IO]
           ,[BoxNo]
           ,[LotNo],RelatedBill1,RelatedBill2)
SELECT NEWID(),'{1}','{2}','{3}',PartNumber,-Qty,'{4}',GETDATE(),'O','{7}',LotNo,'{5}','{6}' FROM dbo.wms_T_OutStockRowDetails WHERE Guid='{0}';", guid, storageSN, areaSN, locationSN, Utils.uContext.UserNamek__BackingField, relatedBill, billNo, NewBoxNo);

                var sqlCmd_js = string.Empty;

                if (relatedBill.StartsWith("VMI"))
                {
                    var sql_js = string.Format(@"INSERT INTO [dbo].[wms_T_InventoryHistory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Qty]
           ,[Creator]
           ,[CreateTime]
           ,[IO]
           ,[BoxNo]
           ,[LotNo],RelatedBill1,RelatedBill2)
SELECT NEWID(),'{1}','{2}','{3}',PartNumber,-Qty,'{4}',GETDATE(),'O','{5}',LotNo,'{6}','{7}' FROM dbo.wms_T_OutStockRowDetails WHERE Guid='{0}';", guid, targetStorage, "0", "0", Utils.uContext.UserNamek__BackingField, boxNo, relatedBill, billNo);

                    var sql_his_js = string.Format(@"INSERT INTO [dbo].[wms_T_InventoryHistory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Qty]
           ,[Creator]
           ,[CreateTime]
           ,[IO]
           ,[BoxNo]
           ,[LotNo],RelatedBill1,RelatedBill2)
SELECT NEWID(),'{1}','{2}','{3}',PartNumber,Qty,'{4}',GETDATE(),'I','{7}',LotNo,'{5}','{6}' FROM dbo.wms_T_OutStockRowDetails WHERE Guid='{0}';", guid, targetStorage, "0", "0", Utils.uContext.UserNamek__BackingField, relatedBill, billNo, NewBoxNo);

                    sqlCmd_js = sql_js + sql_his_js;
                }

                //var sqlCmd = (sql3 + insertHis_Sql+OutHis_Sql + sql + sql2 + sql4+sql5 + sqlCmd_js).Trim(';');
                var sqlCmd = (insertHis_Sql + OutHis_Sql + sql + sql2 + sql4 + sqlCmd_js).Trim(';');
                sqlCmd = @"---开启事务
                begin tran
                begin try  
                " + sqlCmd
+ @"
                end try
                begin catch
                   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
                      rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
                end catch
                if(@@trancount>0)
                commit tran";

                var excuRes = Utils.NMS.ExcuteSQL(sqlCmd);

                if (excuRes > 0)
                {
                    result = true;
                }
            }
            catch (Exception ex)
            {

            }
            return result;
        }

        public static bool BackOutStockRowDetailsEndNoInventory(DataTable dt, string storageSN, string areaSN, string locationSN, string endstorageSN, string endareaSN, string endlocationSN, string NewBoxNo)
        {
            var result = false;
            try
            {
                var detail = dt.Rows[0];
                var billNo = detail["BillNo"].ToString();
                var rowNo = detail["RowNo"].ToString();
                var guid = detail["Guid"].ToString();
                var boxNo = detail["BoxNo"].ToString();
                var qty = Convert.ToInt32(detail["Qty"]);
                var relatedBill = detail["RelatedBill"].ToString();
                var targetStorage = detail["TargetStorage"].ToString();

                string sqlOpenSN = string.Format(@"UPDATE T_Bllb_StorageLocation_tbsl SET Status_Flag='1' WHERE Location_SN='{0}';", endlocationSN);
                string sql3 = string.Format(@"INSERT INTO [dbo].[wms_T_Inventory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Version]
           ,[DateCode]
           ,[Qty]
           ,[EnterTime]
           ,[Status]
           ,[LotNo]
           ,[Unit]
           ,[BoxNo])
SELECT NEWID(),'{1}','{2}','{3}',PartNumber,Version,DateCode,Qty,GETDATE(),'1',LotNo,Unit,'{4}' FROM dbo.wms_T_OutStockRowDetails WHERE Guid='{0}';", guid, endstorageSN, endareaSN, endlocationSN, boxNo);
                string sql = string.Format(@"update [wms_T_OutStockRowDetails]  set BoxNo='{0}'  WHERE Guid='{1}';", NewBoxNo, guid);
                string sql2 = string.Format(@"UPDATE  dbo.wms_T_InStockRowDetails SET  RealQty= ISNULL(RealQty,0)-{0} WHERE BoxNo='{1}' and status='0'; ", qty, NewBoxNo);
                string sql4 = string.Format(@"UPDATE  dbo.wms_T_InStockRowDetails SET  RealQty= ISNULL(RealQty,0)+{0} WHERE BoxNo='{1}' and status='0'; ", qty, boxNo);
                var sql_ser_js = string.Format(@"SELECT * FROM dbo.wms_T_Inventory WHERE location_sn='{0}'", locationSN);
                var serDt = Utils.NMS.GetDataTable(sql_ser_js);
                string sql5 = string.Empty;
                if (serDt.Rows.Count > 0)
                {
                    var serQty = string.IsNullOrEmpty(serDt.Rows[0]["Qty"].ToString()) ? 0 : Convert.ToInt32(serDt.Rows[0]["Qty"]);

                    if (serQty > qty)
                    {
                        sql5 = string.Format(@"UPDATE  wms_T_Inventory SET Qty=Qty-{1} WHERE Guid='{0}';", serDt.Rows[0]["Guid"].ToString(), qty);
                    }
                    else if (serQty == qty)
                    {
                        sql5 = string.Format(@"DELETE FROM wms_T_Inventory WHERE Guid='{0}';", serDt.Rows[0]["Guid"].ToString());
                        sql4 = string.Format(@"UPDATE  T_Bllb_StorageLocation_tbsl SET Status_Flag='0' WHERE Location_SN='{0}';", locationSN);
                    }
                }
                string insertHis_Sql = string.Format(@"INSERT INTO [dbo].[wms_T_InventoryHistory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Qty]
           ,[Creator]
           ,[CreateTime]
           ,[IO]
           ,[BoxNo]
           ,[LotNo])
SELECT NEWID(),'{1}','{2}','{3}',PartNumber,Qty,'{4}',GETDATE(),'I',BoxNo,LotNo FROM dbo.wms_T_OutStockRowDetails WHERE Guid='{0}';", guid, endstorageSN, endareaSN, endlocationSN, Utils.uContext.UserNamek__BackingField);
                string OutHis_Sql = string.Format(@"INSERT INTO [dbo].[wms_T_InventoryHistory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Qty]
           ,[Creator]
           ,[CreateTime]
           ,[IO]
           ,[BoxNo]
           ,[LotNo],RelatedBill1,RelatedBill2)
SELECT NEWID(),'{1}','{2}','{3}',PartNumber,-Qty,'{4}',GETDATE(),'O',{7},LotNo,'{5}','{6}' FROM dbo.wms_T_OutStockRowDetails WHERE Guid='{0}';", guid, storageSN, areaSN, locationSN, Utils.uContext.UserNamek__BackingField, relatedBill, billNo, NewBoxNo);

                var sqlCmd_js = string.Empty;

                if (relatedBill.StartsWith("VMI"))
                {
                    var sql_js = string.Format(@"INSERT INTO [dbo].[wms_T_InventoryHistory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Qty]
           ,[Creator]
           ,[CreateTime]
           ,[IO]
           ,[BoxNo]
           ,[LotNo])
SELECT NEWID(),'{1}','{2}','{3}',PartNumber,-Qty,'{4}',GETDATE(),'O','{5}',LotNo FROM dbo.wms_T_OutStockRowDetails WHERE Guid='{0}';", guid, targetStorage, "0", "0", Utils.uContext.UserNamek__BackingField, boxNo);

                    var sql_his_js = string.Format(@"INSERT INTO [dbo].[wms_T_InventoryHistory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Qty]
           ,[Creator]
           ,[CreateTime]
           ,[IO]
           ,[BoxNo]
           ,[LotNo],RelatedBill1,RelatedBill2)
SELECT NEWID(),'{1}','{2}','{3}',PartNumber,Qty,'{4}',GETDATE(),'I',{7},LotNo,'{5}','{6}' FROM dbo.wms_T_OutStockRowDetails WHERE Guid='{0}';", guid, targetStorage, "0", "0", Utils.uContext.UserNamek__BackingField, relatedBill, billNo, NewBoxNo);

                    sqlCmd_js = sql_js + sql_his_js;
                }

                var sqlCmd = (sqlOpenSN + sql3 + insertHis_Sql + OutHis_Sql + sql + sql2 + sql4 + sql5 + sqlCmd_js).Trim(';');

                sqlCmd = @"---开启事务
                begin tran
                begin try  
                " + sqlCmd
+ @"
                end try
                begin catch
                   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
                      rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
                end catch
                if(@@trancount>0)
                commit tran";

                var excuRes = Utils.NMS.ExcuteSQL(sqlCmd);

                if (excuRes > 0)
                {
                    result = true;
                }
            }
            catch (Exception ex)
            {

            }
            return result;
        }

        /// <summary>
        /// 获取入库单下所有箱数据
        /// </summary>
        /// <param name="billNo">入库单据号</param>
        /// <returns>箱数据</returns>
        public static DataTable GetBoxNoList(string billNo)
        {
            string sql = string.Format(@"
              SELECT a.BillNo, a.OutStatus, b.BoxNo, b.Status, b.PartNumber, SUM(b.RealQty) AS RealQty
                FROM dbo.wms_T_InStock a
                LEFT JOIN dbo.wms_T_InStockRowDetails b
                  ON a.BillNo = b.BillNo
                 AND b.status<>'H'
               WHERE a.BillNo='{0}'
               GROUP BY a.BillNo, a.OutStatus, b.BoxNo, b.PartNumber, b.Status
               ORDER BY b.BoxNo", billNo);

            var dt = Utils.NMS.GetDataTable(sql);

            return dt;
        }

        /// <summary>
        /// 获取指定入库单据号下，指定箱号的箱数据
        /// </summary>
        /// <param name="billNo">入库单据号</param>
        /// <param name="boxNo">箱号</param>
        /// <returns>箱数据</returns>
        public static DataTable GetBoxInfo(string billNo, string boxNo)
        {
            string sqlcmd = string.Format(@"SELECT BoxNo, Status, PartNumber, SUM(RealQty) AS RealQty, SUM(PutawayQty) AS PutawayQty
                                              FROM [dbo].[wms_T_InStockRowDetails]
                                             WHERE BillNo='{0}'
                                               AND BoxNo='{1}'
                                               AND Status<>'H'
                                             GROUP BY BoxNo, Status, PartNumber
                                             ORDER BY Status", billNo, boxNo);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }

        /// <summary>
        /// 更新入库单为已出库
        /// </summary>
        /// <param name="billNo">入库单据号</param>
        /// <returns></returns>
        public static bool UpdateBillNoOutStock(string billNo)
        {
            string sqlcmd = string.Format(@"UPDATE [dbo].[wms_T_InStock] SET [OutStatus] = '1' WHERE BillNo='{0}'", billNo);
            var excuRes = Utils.NMS.ExcuteSQL(sqlcmd);
            return excuRes > 0;
        }
    }
}
